package quary;

import java.sql.*;
import java.util.ArrayList;

import exception.DataLengthException;

public class Quary {

	public static final Integer NULL = -1;
	
	private String database;
	private String username;
	private String password;

	private Connection connection;
	
	public Quary(String database, String username, String password) {
		this.database = database;
		this.username = username;
		this.password = password;
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			System.err.println("Finner ikke MySQL driveren!");
			e.printStackTrace();
			System.exit(1);
		}
	}
		
	
	
	public int deleteFromDatabase(String deleteQuary) {
		int deletedRows = 0;
		
		try {
			this.openConnection();
			
			Statement st = connection.createStatement();
			deletedRows = st.executeUpdate(deleteQuary);
			
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			this.closeConnection();
			System.out.println("Delete Quary: " + deleteQuary);
		}
		return deletedRows;
	}
	
	public int insertIntoDatabase(String insertQuary) {
		int insertedRows = 0;
	
		try {
			this.openConnection();
	    	Statement st = connection.createStatement();
	    	insertedRows = st.executeUpdate(insertQuary);	
	    
	    } catch (SQLException e) {
	    	System.out.println(e.getMessage());
		} finally {
			this.closeConnection();
			System.out.println("Insert Quary: " + insertQuary);
		}
		
		return insertedRows;
	}
		
	public int updateDatabase(String updateQuary) {
		int updatedRows = 0;
		
		try {
			this.openConnection();
	    	Statement st = connection.createStatement();
	    	updatedRows = st.executeUpdate(updateQuary);	
	    
	    } catch (SQLException e) {
	    	System.out.println(e.getMessage());
		} finally {
			this.closeConnection();
			System.out.println("Update Quary: " + updateQuary);
		}
		
		return updatedRows;
	}	

	public Object[][] getSelectionData(String selectionQuary) {
		Object[][] data = null;
		
		try {
			this.openConnection();
			
			Statement sqlStatement = connection.createStatement();
			ResultSet set = sqlStatement.executeQuery(selectionQuary);
			
			set.last();
			
			int columnCount = set.getMetaData().getColumnCount();
			data = new Object[set.getRow()][columnCount];
			
			set.beforeFirst();
			
			while(set.next()) {
				for (int i = 0; i < columnCount; i++)
					data[set.getRow() - 1][i] = set.getObject(i+1);
				
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			this.closeConnection();
			System.out.println("Select Quary: " + selectionQuary);
		}
		return data;
	}
	
	/**
	 * Build Delete Quary
	 * 
	 * @param table
	 * @param keyVar
	 * @param keyArray
	 * @return
	 */
	public String buildDeleteQuary(String table, String keyVar, Object[] keyArray) {
		String deleteQuary = 
			"DELETE " +
			"FROM " +
				table + " " +
			"WHERE " +
				keyVar + " IN ('" + keyArray[0];
	
		for (int i = 1; i < keyArray.length; i++) {
			deleteQuary += "','" + keyArray[i];
		}
		deleteQuary += "');";
		
		return deleteQuary;
	}
	
	/**
	 * Build Insert Quary
	 * 
	 * @param table
	 * @param variables
	 * @param data
	 * @return
	 */
	public String buildInsertQuary(String table, String[] variables, Object[] data) {
		int varLength = variables.length;
		int dataLength = data.length;
		
		if (varLength != dataLength)
			throw new DataLengthException(
					"Variables length differs from Data length " + varLength + " != " + dataLength);
				
		ArrayList<Object> tempData = new ArrayList<Object>();
		ArrayList<String> tempVariables = new ArrayList<String>();
		
		for (int i = 0; i < dataLength; i++) {
			if (data[i] != null) {
				tempData.add(data[i]);
				tempVariables.add(variables[i]);
			}
		}
		
		String insertQuary = 
			"INSERT INTO " + table + 
			" (";
		
		for (int i = 0; i < tempVariables.size(); i++) {
			if (i == tempVariables.size() - 1) 
				insertQuary += tempVariables.get(i) + ") ";
			else
				insertQuary += tempVariables.get(i) + ", ";
		}
		
		insertQuary += 
			"VALUES ('";
		
		for (int i = 0; i < tempData.size(); i++) {
			if (i == tempData.size() - 1) 
				insertQuary += tempData.get(i) + "');";
			else
				insertQuary += tempData.get(i) + "','";
		}
		
		return insertQuary;
	}
	
	/**
	 * Build Update Quary
	 * 
	 * @param table
	 * @param variables
	 * @param data
	 * @param keyVar
	 * @param keyArray
	 * @return
	 */
	public String buildUpdateQuary(String table, String[] variables, Object[] data, String keyVar, Object[] keyArray) {
		int varLength = variables.length;
		int dataLength = data.length;
		
		if (varLength != dataLength)
			throw new DataLengthException(
					"Variables length differs from Data length " + varLength + " != " + dataLength);
		
		ArrayList<Object> tempData = new ArrayList<Object>();
		ArrayList<String> tempVariables = new ArrayList<String>();
		
		for (int i = 0; i < dataLength; i++) {
			if (data[i] != null) {
				tempData.add(data[i]);
				tempVariables.add(variables[i]);
			}
		}
		
		String updateQuary = 
			"UPDATE " + table + 
			" SET ";
		
		updateQuary += tempVariables.get(0);
		if (tempData.get(0) == NULL)
			updateQuary += " = " + tempData.get(0);
		else
			updateQuary += " = '" + tempData.get(0);
		
		int size = tempVariables.size();
		for (int i = 1; i < size; i++) {
			if (tempData.get(i -1) == NULL)
				updateQuary += ", " + tempVariables.get(i) + " = ";
			else
				updateQuary += "', " + tempVariables.get(i) + " = ";
			
			updateQuary += tempData.get(i) == NULL ? "NULL" : "'" + tempData.get(i);
		}
		
		if (tempData.get(size -1) == NULL)
			updateQuary += " ";
		else
			updateQuary += "' ";
		
		updateQuary +=
			"WHERE " +
				keyVar + " IN ('" + keyArray[0];

		for (int i = 1; i < keyArray.length; i++) {
			updateQuary += "','" + keyArray[i];
		}
		updateQuary += "');";
		
		return updateQuary;
	}

	/**
	 * Build Select Quary
	 * 
	 * @param variables
	 * @param from
	 * @param on
	 * @param keyVar
	 * @param key
	 * @param orderBy
	 * @param varMerge
	 * @return
	 */
	public String buildSelectQuary(String[] variables, String[] from, String[][] on, String keyVar, int key, String orderBy, int[][] varMerge) {
		String selectQuary = 
			"SELECT ";
			
		for (int i = 0; i < variables.length; i++) {
			boolean merge = false;
			
			if (varMerge != null) {
				for (int j = 0; j < varMerge.length; j++) {
					if (varMerge[j][0] <= i && i <= varMerge[j][varMerge[j].length-1]) {
						merge = true;
						if (varMerge[j][0] == i) {
							if (i > 0) {
								selectQuary += ", CONCAT_WS(' ', " +
								variables[i];
							}
							else {
								selectQuary += "CONCAT_WS(' ', " +
								variables[i];
							}
								
						}
						else if (varMerge[j][varMerge[j].length-1] == i) {
							selectQuary += ", " +
								variables[i] + ") ";
						}
						else {
							selectQuary += ", " + variables[i];
						}
					}
				}
			}
			
			if (!merge) {
				if (i == 0) {
					selectQuary += variables[i] + " ";
				}
				else
					selectQuary += "," + variables[i] + " ";
			}
		}
		
		selectQuary += 
			"FROM " +
				from[0] + " ";
		
		for (int i = 1; i < from.length; i++) {
			selectQuary +=
				"JOIN " + 
					from[i] + " ON " + 
						on[i-1][0] + " = " + on[i-1][1] + " ";
			
		}
		
		if (keyVar != null) {
			selectQuary += 
				"WHERE " +
					keyVar + " = " + key;
		}
		
		if (orderBy != null) {
			selectQuary += " " +
				"ORDER BY " +
					orderBy;
		}
		
		selectQuary += ";";
		
		return selectQuary;
	}
	
	private void openConnection() throws SQLException {
		connection = DriverManager.getConnection(
				this.database, 
				this.username,
				this.password
		);
	}
	
	private void closeConnection() {
		try {
			connection.close();
			connection = null;
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
